﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System.IO;
using iTextSharp.text.pdf;
using HMS.Logging;

namespace HMS.Common
{
   
    public class Export
    {        
       
        public static MemoryStream CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
        {
            try
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(ListToDataTable(list));

                return CreateExcelDocument(ds);
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$Export$", "CreateExcelDocument", "Error in CreateExcelDocument", "", ex);
                throw ex;
            }
        }

        //  This function is taken from: http://www.codeguru.com/forum/showthread.php?t=450171
        public static DataTable ListToDataTable<T>(List<T> list)
        {
            try
            {

                DataTable dt = new DataTable();

                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    dt.Columns.Add(new DataColumn(info.Name, info.PropertyType));
                }
                foreach (T t in list)
                {
                    DataRow row = dt.NewRow();
                    foreach (PropertyInfo info in typeof(T).GetProperties())
                    {
                        row[info.Name] = info.GetValue(t, null);
                    }
                    dt.Rows.Add(row);
                }
                return dt;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$Export$", "ListToDataTable", "Error in ListToDataTable", "", ex);
                throw ex;
            }
        }

        public static MemoryStream CreateExcelDocument(DataTable dt, string xlsxFilePath)
        {
            try
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);

                return CreateExcelDocument(ds);
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$Export$", "CreateExcelDocument(dt,path)", "Error in CreateExcelDocument(dt,path)", "", ex);
                throw ex;
            }
        }

        public static MemoryStream CreateExcelDocument(DataSet ds)
        {

            try
            {
                //  Note: for this Export to Excel class to work, you need to have References to two .dll files:
                //      DocumentFormat.OpenXml.dll
                //      WindowBase.dll
                // 
                //  The "DocumentFormat.OpenXml.dll" file can be downloaded from Microsoft, just Google "Open XML SDK" to find the latest download location.  
                //  It typically installs the relevant file to: "C:\Program Files\Open XML SDK\V2.0\lib"
                //
                //  The WindowBase.dll file is installed with Microsoft Framework, typically into the directory:
                //  "C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\WindowsBase.dll"
                //
                //  References:
                //      "How to create an Excel 2007 file, from scratch:"
                //      http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx
                //      "Writing data into excel document using openxml"
                //      http://www.prowareness.com/blog/?p=476
                //
                MemoryStream xlsStream = new MemoryStream();
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(xlsStream, SpreadsheetDocumentType.Workbook, true))
                {
                    // Create the Workbook
                    spreadSheet.AddWorkbookPart();
                    spreadSheet.WorkbookPart.Workbook = new Workbook();

                    // A Workbook must only have exactly one <Sheets> section
                    spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());

                    //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
                    uint worksheetNumber = 1;
                    foreach (DataTable dt in ds.Tables)
                    {
                        WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                        newWorksheetPart.Worksheet = new Worksheet();

                        //  Create a new Excel worksheet 
                        SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData());

                        //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
                        //
                        //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
                        //  cells of data, we'll know if to write Text values or Numeric cell values.
                        int numberOfColumns = dt.Columns.Count;
                        bool[] IsNumericColumn = new bool[numberOfColumns];

                        string[] excelColumnNames = new string[numberOfColumns];
                        for (int n = 0; n < numberOfColumns; n++)
                            excelColumnNames[n] = GetExcelColumnName(n);

                        int rowIndex = 1;
                        Row newRow = sheetData.AppendChild(new Row());
                        for (int colInx = 0; colInx < numberOfColumns; colInx++)
                        {
                            DataColumn col = dt.Columns[colInx];
                            Cell newHeaderCell = CreateTextCell(excelColumnNames[colInx], rowIndex, col.ColumnName);
                            IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal");     //  eg "System.String" or "System.Decimal"
                            newRow.AppendChild(newHeaderCell);
                        }

                        //  Now, step through each row of data in our DataTable...
                        Cell newCell;
                        foreach (DataRow dr in dt.Rows)
                        {
                            // ...create a new row, and append a set of this row's data to it.
                            newRow = sheetData.AppendChild(new Row());
                            ++rowIndex;

                            for (int colInx = 0; colInx < numberOfColumns; colInx++)
                            {
                                string cellValue = dr.ItemArray[colInx].ToString();

                                // Create cell with data
                                if (IsNumericColumn[colInx] && !string.IsNullOrEmpty(cellValue))
                                    newCell = CreateNumericCell(excelColumnNames[colInx], rowIndex, cellValue);
                                else
                                    newCell = CreateTextCell(excelColumnNames[colInx], rowIndex, cellValue);

                                newRow.AppendChild(newCell);
                            }
                        }
                        newWorksheetPart.Worksheet.Save();

                        //  Link this worksheet to our workbook
                        spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
                        {
                            Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
                            SheetId = worksheetNumber++,
                            Name = dt.TableName
                        });
                    }   // foreach (DataTable..)

                    //spreadSheet.WorkbookPart.Workbook.Save();

                }   // using (SpreadsheetDocument spreadSheet..)

                return xlsStream;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$Export$", "CreateExcelDocument(ds)", "Error in CreateExcelDocument(ds)", "", ex);
                throw ex;
            }
        }

        private static Cell CreateTextCell(string excelColumnName, int excelRowIndex, string cellValue)
        {


            try
            {
                //  Create an Excel cell, containing a Text value
                Cell c = new Cell();
                c.DataType = CellValues.InlineString;
                c.CellReference = excelColumnName + excelRowIndex.ToString();

                //Add text to the text cell.
                InlineString inlineString = new InlineString();
                Text t = new Text();
                t.Text = cellValue.ToString();
                inlineString.AppendChild(t);
                c.AppendChild(inlineString);

                return c;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$Export$", "CreateTextCell", "Error in CreateTextCell", "", ex);
                throw ex;
            }
        }

        private static Cell CreateNumericCell(string excelColumnName, int excelRowIndex, string cellValue)
        {

            try
            {

                //  Create an Excel cell, containing a numeric value
                Cell c = new Cell();
                c.DataType = CellValues.Number;
                c.CellReference = excelColumnName + excelRowIndex.ToString();

                if (string.IsNullOrEmpty(cellValue))
                    return c;

                decimal decimalValue = 0;
                if (!decimal.TryParse(cellValue, out decimalValue))
                    return c;

                //  If we don't specifically set a number of decimal places, sometimes it'll create invalid Excel 
                //  files ("Do you wish to repair..")
                CellValue v = new CellValue();
                v.Text = decimalValue.ToString("0.000000");
                c.AppendChild(v);

                return c;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$Export$", "CreateNumericCell", "Error in CreateNumericCell", "", ex);
                throw ex;
            }
        }

        private static string GetExcelColumnName(int columnIndex)
        {

            try
            {
                //  Convert a zero-based column index into an Excel column reference (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)
                //  Each Excel cell we write must have the cell name stored with it.
                //
                if (columnIndex < 26)
                    return ((char)('A' + columnIndex)).ToString();

                char firstChar = (char)('A' + (columnIndex / 26));
                char secondChar = (char)('A' + (columnIndex % 26));

                return string.Format("{0}{1}", firstChar, secondChar);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$Export$", "GetExcelColumnName", "Error in GetExcelColumnName", "", ex);
                throw ex;
            }
        }

        public static MemoryStream CreatePDFDocument(DataTable dt)
        {


            try
            {

                iTextSharp.text.Document pdfDoc = new iTextSharp.text.Document();
                MemoryStream pdfStream = new MemoryStream();
                PdfWriter pdfWriter = PdfWriter.GetInstance(pdfDoc, pdfStream);

                pdfDoc.Open();//Open Document to write
                pdfDoc.NewPage();

                iTextSharp.text.Font font8 = iTextSharp.text.FontFactory.GetFont("ARIAL", 7);

                PdfPTable PdfTable = new PdfPTable(dt.Columns.Count);
                PdfPCell PdfPCell = null;

                //Add Header of the pdf table
                for (int column = 0; column < dt.Columns.Count; column++)
                {
                    PdfPCell = new PdfPCell(new iTextSharp.text.Phrase(new iTextSharp.text.Chunk(dt.Columns[column].Caption, font8)));
                    PdfTable.AddCell(PdfPCell);
                }

                //How add the data from datatable to pdf table
                for (int rows = 0; rows < dt.Rows.Count; rows++)
                {
                    for (int column = 0; column < dt.Columns.Count; column++)
                    {
                        PdfPCell = new PdfPCell(new iTextSharp.text.Phrase(new iTextSharp.text.Chunk(dt.Rows[rows][column].ToString(), font8)));
                        PdfTable.AddCell(PdfPCell);
                    }
                }

                PdfTable.SpacingBefore = 15f; // Give some space after the text or it may overlap the table            
                pdfDoc.Add(PdfTable); // add pdf table to the document
                pdfDoc.Close();
                pdfWriter.Close();

                return pdfStream;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$Export$", "CreatePDFDocument", "Error in CreatePDFDocument", "", ex);
                throw ex;
            }
        }
    }
}
